﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace canyinguanlixitong
{
    public partial class Formxitong : Form
    {
        //连接数据库
        string strconn = "Data Source=.;Initial Catalog=Supermarket management system;Integrated Security=True";
        public Formxitong()
        {
            InitializeComponent();
        }
        SqlDataAdapter apa = new SqlDataAdapter();
        DataSet dj = new DataSet("czy");
        DataSet dd = new DataSet("Goods");
        DataSet dl = new DataSet("ha");
        DataSet ds = new DataSet("Usertype");
        DataSet dv = new DataSet("Commodity type");
        DataSet dg = new DataSet("Room type");
        DataSet dt = new DataSet("VIPtype");
        DataSet da = new DataSet("fws");
        private void buttontianjia_Click(object sender, EventArgs e)
        {
            frmaddroom far = new frmaddroom();
            far.Show();
        }

        private void tabPage4_Click(object sender, EventArgs e)
        {

        }

        private void tabPage6_Click(object sender, EventArgs e)
        {

        }

        private void tabPage10_Click(object sender, EventArgs e)
        {

        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void tabPage12_Click(object sender, EventArgs e)
        {

        }

        private void button11_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dd.Tables["Goods"]!= null)
            {
                dd.Tables["Goods"].Clear();
            }
            string sql = @"SELECT  [Commodity type].slLei, commodity.spID, commodity.spName, commodity.spJia
FROM      commodity INNER JOIN
                [Commodity type] ON commodity.spLei = [Commodity type].SlId";
            SqlDataAdapter ada = new SqlDataAdapter(sql, conn);
            ada.Fill(dd, "Goods");
            DGVGL1.DataSource = dd.Tables["Goods"];
        }

        private void buttonxiugai_Click(object sender, EventArgs e)
        {
            frmupdateroom fdr = new frmupdateroom();
            fdr.stuon = Convert.ToInt32(this.dgvfj1.SelectedRows[0].Cells["hjl3"].Value);
            fdr.Show();
        }

        private void buttondange_Click(object sender, EventArgs e)
        {
            frmaddtable frt = new frmaddtable();
            frt.Show();
        }

        private void buttonshanchulo_Click(object sender, EventArgs e)
        {
            frm fm = new frm();
            fm.Show();
        }

        private void buttonpiliang_Click(object sender, EventArgs e)
        {
            frmaddrooms fars = new frmaddrooms();
            fars.Show();
        }

        private void buttonxiugailo_Click(object sender, EventArgs e)
        {
            frmupdatetable fut = new frmupdatetable();
            fut.Show();
        }

        private void 添加房间类ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmaddroom far = new frmaddroom();
            far.Show();
        }

        private void 修改房间类ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmupdateroom fudr = new frmupdateroom();
            fudr.Show();
        }

        private void 删除房间类ToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void 单个ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmaddtable fat = new frmaddtable();
            fat.Show();
        }

        private void 批量添加台ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmaddrooms fars = new frmaddrooms();
            fars.Show();
        }

        private void 修改台ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmupdatetable fudt = new frmupdatetable();
            fudt.Show();
        }

        private void buttonadd_Click(object sender, EventArgs e)
        {

            frmaddgoods fgs = new frmaddgoods();
            fgs.Show();
        }

        private void buttonxg_Click(object sender, EventArgs e)
        {
            frmupdategoods fdg = new frmupdategoods();

            fdg.stu1 = Convert.ToInt32(this.DGVSP1.SelectedRows[0].Cells["Column1"].Value);
            fdg.Show();
        }

        private void buttontianjia2_Click(object sender, EventArgs e)
        {
            frmaddspcs fps = new frmaddspcs();
            fps.Show();
        }

        private void buttonxiugai2_Click(object sender, EventArgs e)
        {
            frmupdatesplei fel = new frmupdatesplei();

            fel.stuo = Convert.ToInt32(this.DGVGL1.SelectedRows[0].Cells["dataGridViewTextBoxColumn1"].Value);
            fel.Show();
        }

        private void buttonshezhi_Click(object sender, EventArgs e)
        {
            frmmoreset frm = new frmmoreset();
            frm.Show();
        }

        private void buttonfi_Click(object sender, EventArgs e)
        {

            frmzhekou fzk = new frmzhekou();
            fzk.Show();
        }

        private void buttontd_Click(object sender, EventArgs e)
        {
            frmaddsever fsr = new frmaddsever();
            fsr.Show();
        }
        //修改
        private void button2_Click(object sender, EventArgs e)
        {
            frmupdatesevers fds = new frmupdatesevers();
            fds.stu = Convert.ToInt32(this.dgvv1.SelectedRows[0].Cells["dataGridViewTextBoxColumn5"].Value);
            fds.Show();
        }

        private void buttonxgdj_Click(object sender, EventArgs e)
        {
            frmupdatesever fus = new frmupdatesever();
            fus.stu = Convert.ToInt32(this.DGVfws.SelectedRows[0].Cells["cols"].Value);
            fus.Show();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            frmaddsevers fss = new frmaddsevers();

            fss.Show();
        }

        private void buttonbaocun_Click(object sender, EventArgs e)
        {
            frmaddviplevel favl = new frmaddviplevel();
            favl.Show();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            addpank ap = new addpank();
            ap.Show();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            frmupdatepank fup = new frmupdatepank();
            fup.Show();
        }
        //删除房间类型
        private void buttonshanchu_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.dgvfj1.SelectedRows[0].Cells["hjl3"].Value);
            if (dgvfj1.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认要删除吗", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                    string sql = "delete dbo.Roombiao where fangID='" + dgv + "'Delete [Room type] where fangID='" + dgv + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                        sch();
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }
                }
            }
        }

        private void buttondazhe_Click(object sender, EventArgs e)
        {
            frm fm = new frm();
            fm.Show();
        }
        //删除
        private void buttondelete_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.DGVSP1.SelectedRows[0].Cells["Column1"].Value);
            if (DGVSP1.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认要删除吗", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                string sql = @"DELETE FROM [Commodity type]
      FROM      [Commodity type] INNER JOIN
                [Commodity type] AS [Commodity type_1] ON [Commodity type].SlId = [Commodity type_1].SlId WHERE [Commodity type].SlId='" + dgv + "'";            
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                       
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }
                }
            }
        }

        private void buttonshanchu2_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.DGVGL1.SelectedRows[0].Cells["dataGridViewTextBoxColumn1"].Value);
            if (DGVGL1.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认要删除吗", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                    string sql = "Delete [commodity] where spID='"+dgv+"' delete [Order food] where sptID='"+dgv+"'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                        
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }
                }
            }
        }

        private void buttoncaidan_Click(object sender, EventArgs e)
        {
            frm fm = new frm();
            fm.Show();
        }

        //删除
        private void button3_Click(object sender, EventArgs e)
        {
            frm frm = new frm();
            frm.Show();
        }

        private void dgvfj1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
        //查询房间类型
        public void sch()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dg.Tables["Room type"] != null)
            {
                dg.Tables["Room type"].Clear();
            }
            string sql = "Select fangName,fangJia,fangID from [Room type]";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(dg, "Room type");
            dgvfj1.DataSource = dg.Tables["Room type"];
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void Formxitong_Load(object sender, EventArgs e)
        {
            sea();
            fwsdj();
            seargoods();
            sch();
            sear();
            searchvip();
            czy();
            searchsevers();
        }
        //查询操作员
        public void czy()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dj.Tables["czy"] != null)
            {
                dj.Tables["czy"].Clear();
            }
            string sql = "select userID,id,pwd FROM [User]";
            SqlDataAdapter a = new SqlDataAdapter(sql, conn);
            a.Fill(dj, "czy");
            dgtt.DataSource = dj.Tables["czy"];
        }
        //查询服务生等级
        public void fwsdj()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (da.Tables["fws"] != null)
            {
                da.Tables["fws"].Clear();
            }
            string sql = "SELECT fwsbh,fwsdj from fws";
            SqlDataAdapter a = new SqlDataAdapter(sql, conn);
            a.Fill(da, "fws");
            DGVfws.DataSource = da.Tables["fws"];

        }
        //查询商品
        public void seargoods()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dl.Tables["Goods"] != null)
            {
                dl.Tables["Goods"].Clear();
            }
            string sql = @"SELECT   [Commodity type].slLei, commodity.spID, commodity.spName, commodity.spJia
FROM      commodity INNER JOIN
                [Commodity type] ON commodity.spLei = [Commodity type].SlId";
            SqlDataAdapter ada = new SqlDataAdapter(sql, conn);
            ada.Fill(dd, "Goods");
            DGVGL1.DataSource = dd.Tables["Goods"];

        }

        //查询房间类型
        public void sea()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dl.Tables["ha"] != null)
            {
                dl.Tables["ha"].Clear();
            }
            string sql = @"        SELECT   Tablecondition.czName, [Room type].fangName, Roombiao.fangBH
    FROM      [Room type] INNER JOIN
                    Roombiao ON [Room type].fangID = Roombiao.fangID INNER JOIN
            
 [Table] ON Roombiao.fangCz = [Table].czID INNER JOIN
                    Tablecondition ON [Table].cz = Tablecondition.cz ";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(dl, "ha");
            dgvfj2.DataSource = dl.Tables["ha"];

        }
        //查询商品类别
        public void sear()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dv.Tables["Commodity type"] != null)
            {
                dv.Tables["Commodity type"].Clear();
            }
            string sql = "select SlId,slLei from [Commodity type] ";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(dv, "Commodity type");
            DGVSP1.DataSource = dv.Tables["Commodity type"];
        }
        //查询服务员
        public void searchsevers()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (ds.Tables["Usertype"] != null)
            {
                ds.Tables["Usertype"].Clear();
            }
            string sql = "select userID,userName,userSex,userDian,userAdd,userBorn from dbo.UserTpye";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(ds, "Usertype");
            dgvv1.DataSource = ds.Tables["Usertype"];
        }
        //查询 vip
        public void searchvip()
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dt.Tables["VIPtype"] != null)
            {
                dt.Tables["VIPtype"].Clear();
            }
            string sql = "select vipLeiHao,vipLeiName from VIPtype";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(dt, "VIPtype");
            dgvvip.DataSource = dt.Tables["VIPtype"];
        }
        //删除服务员
        private void s删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.dgvv1.SelectedRows[0].Cells["dataGridViewTextBoxColumn5"].Value);
            if (dgvv1.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认要删除吗", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                    string sql = "Delete UserTpye where userID='" + dgv + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                        searchsevers();
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }
                }
            }
        }

        private void DGVSP1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void tabPage7_Click(object sender, EventArgs e)
        {

        }

        private void dgvfj2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dtvcp_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void DGVGL1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void tabPage3_Click(object sender, EventArgs e)
        {


        }


        //删除服务生等级
        private void buttonshanchudj_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.DGVfws.SelectedRows[0].Cells["cols"].Value);
            if (DGVfws.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认要删除吗", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                    string sql = "Delete fws where fwsbh='" + dgv + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                        fwsdj();
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }
                }
            }
        }

        private void dataGridView4_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void buttonADD2_Click(object sender, EventArgs e)
        {
            frmaddczy adc = new frmaddczy();
            adc.Show();
        }
        //删除操作员

        private void buttonscczy_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.dgtt.SelectedRows[0].Cells["czybh"].Value);
            if (this.dgtt.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认删除吗？", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                    string sql = "DELETE [User] where userID='" + dgv + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                        czy();
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }

                }
            }
        }

        private void DGVfws_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void buttonxgczy_Click(object sender, EventArgs e)
        {
            frmupdateczy fdz = new frmupdateczy();
            fdz.stuno = Convert.ToInt32(this.dgtt.SelectedRows[0].Cells["czybh"].Value);
            fdz.Show();
        }
        //刷新操作员
        private void btnag_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dj.Tables["czy"] != null)
            {
                dj.Tables["czy"].Clear();
            }
            string sql = "select userID,id,pwd FROM [User]";
            SqlDataAdapter a = new SqlDataAdapter(sql, conn);
            a.Fill(dj, "czy");
            dgtt.DataSource = dj.Tables["czy"];
        }
        //刷新服务员
        private void button4_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (ds.Tables["Usertype"] != null)
            {
                ds.Tables["Usertype"].Clear();
            }
            string sql = "select userID,userName,userSex,userDian,userAdd,userBorn from dbo.UserTpye";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(ds, "Usertype");
            dgvv1.DataSource = ds.Tables["Usertype"];
        }
        //刷新商品类别
        private void button10_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dv.Tables["Commodity type"] != null)
            {
                dv.Tables["Commodity type"].Clear();
            }
            string sql = "select SlId,slLei from [Commodity type] ";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(dv, "Commodity type");
            DGVSP1.DataSource = dv.Tables["Commodity type"];
        }
        //刷新商品
        private void button11_Click_1(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dl.Tables["Goods"] != null)
            {
                dl.Tables["Good ws"].Clear();
            }
            string sql = @"SELECT   [Commodity type].slLei, commodity.spID, commodity.spName, commodity.spJia
FROM      commodity INNER JOIN
                [Commodity type] ON commodity.spLei = [Commodity type].SlId";
            SqlDataAdapter ada = new SqlDataAdapter(sql, conn);
            ada.Fill(dd, "Goods");
            DGVGL1.DataSource = dd.Tables["Goods"];
        }

        private void buttontianjia_Click_1(object sender, EventArgs e)
        {
            frmaddroom far = new frmaddroom();
            far.Show();
        }

        private void buttonxiugai_Click_1(object sender, EventArgs e)
        {
            frmupdateroom fur = new frmupdateroom();
            
            fur.Show();
        }

        private void buttonshanchu_Click_1(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            int dgv = Convert.ToInt32(this.dgvfj1.SelectedRows[0].Cells["hjl3"].Value);
            if (dgvfj1.SelectedRows.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认要删除吗", "提示", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    conn.Open();
                    string sql = "delete dbo.Roombiao where fangID='" + dgv + "'Delete [Room type] where fangID='" + dgv + "'";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("删除成功");
                        sch();
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }
                }
            }
        }

        private void buttonbaocun_Click_1(object sender, EventArgs e)
        {
            MessageBox.Show("保存成功");
        }

        private void buttondazhe_Click_1(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (dg.Tables["Room type"] != null)
            {
                dg.Tables["Room type"].Clear();
            }
            string sql = "Select fangName,fangJia,fangID from [Room type]";
            SqlDataAdapter asa = new SqlDataAdapter(sql, conn);
            asa.Fill(dg, "Room type");
            dgvfj1.DataSource = dg.Tables["Room type"];
        }

        private void buttonshuax_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strconn);
            if (da.Tables["fws"] != null)
            {
                da.Tables["fws"].Clear();
            }
            string sql = "SELECT fwsbh,fwsdj from fws";
            SqlDataAdapter a = new SqlDataAdapter(sql, conn);
            a.Fill(da, "fws");
            DGVfws.DataSource = da.Tables["fws"];
        }

        private void tabPage6_Click_1(object sender, EventArgs e)
        {

        }

        private void button5_Click(object sender, EventArgs e)
        {
            MessageBox.Show("保存成功");
        }
    }
}